<html>
<head>
<title>Database Query</title>
</head>

<body>
<h1>Database Query Web Interface</h1>
Victor Perez <br />
Ver 1.0 4/22/2012<br />
<p>
Type a valid mySQL query below. <br />
Only SELECT and SHOW queries are supported at the moment.
</p>

<p>
<!--<form name="qform" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">-->
<form name="qform" method="post" action="query.php">
<textarea name="query" cols="60" rows="8">
<?php

    //Check to preserve previously entered query
    if (array_key_exists('submitted', $_POST)) 
    {
	$query=$_POST["query"];
	$submitted=TRUE;
    }
    else {
        //If no previous query has been entered then populate the text box
	//with a default query.
        $query="SHOW tables";
	$submitted=FALSE;	 
    }
    //Add the query to the text box
    print($query);
?></textarea><br />
<input type="hidden" name="submitted" value="1"/> 
<input type="submit" value="Submit" />
</form>
</p>

<?php

if ($submitted) {

    //Check if this is a valid query (SELECT and SHOW only)
    if (is_valid_query($query)) {
        //Print results header
        print("<h3>Database query results:</h3>\n");

	//Get a connection to the database server and select a database to
	//work with.
    	$db_connection= db_con();

    	//Issue the user query which has already been validated
    	$rsrc = mysql_query($query, $db_connection);

    	//Extract the number of attributes for the query result realation
    	$attr_num = mysql_num_fields($rsrc);

	//Print HTML table parameters
    	print("<table border=1 cellspacing=1 cellpadding=2>\n");

    	//Print relation attribute names
    	print("<tr align=center>");
        for($i=0; $i<$attr_num; $i++) {
            $attr_name=mysql_field_name($rsrc, $i);
            print ("<td><b>$attr_name</b></td>");
        }
        print("</tr>\n");

	//Fetch each table row 
        while($row = mysql_fetch_row($rsrc)) {
        
	    //Print HTML table row heading
	    print("<tr align=center>");

	    //Print each attribute of the table row (tuple)
            for($col=0; $col<$attr_num; $col++) {
	        $field_val=$row[$col];
	        if (is_null($field_val)) {
	            $field_val="N/A";
	        }
	        print ("<td>$field_val</td>");
	    }

	    //Close the HTML row
	    print "</tr>\n";
        }

        //Close the HTML table
        print("</table>");

        //Close the connection once were are done using it
        mysql_close($db_connection);
    }
    else {
        //User specified an invalid query
	print("<p><small>Only SELECT and SHOW mySQL queries supported at this time.</small></p>\n");
    }
}


//Validate whether the given query string is supported
function is_valid_query($query) {

    //Regular expression pattern for valid mySQL queries suppoerted
    //for this part of the project.
    $valid_q_pattern='/^\s*(select|show)\s+.*/i';

    //Check if query is a valid expression
    $valid_q = preg_match($valid_q_pattern, $query);
    return $valid_q;
}

//Setup a connection to the database server, and select the database to work 
//with.
function db_con() {

    //Initiate a connection to the cs143 database server
    $db_connection = mysql_connect("localhost", "cs143", "");

    //Select the database we will be interacting with
    mysql_select_db("CS143", $db_connection);

    return $db_connection;
}

?>

</body>
</html>
